<?php 
require_once dirname(__FILE__) . '/DbUtils.class.php';
require_once dirname(__FILE__) . '/metadata.php';
require_once dirname(__FILE__) . '/config.php';
require_once dirname(__FILE__) . '/includes/MiscUtils.class.php';
session_name(SESSION);
session_start();
if (!isset($_SESSION[CLIENT])) {
  return;
}
$myPdo = DbUtils::createPdoInst();

$class = MiscUtils::getParam('class', 0);
$date = MiscUtils::getParam('date', '0000-00-00');

$join = 'JOIN ' . TABLE_NAME_PREFIX_PLUGIN . 'student_enrolled_classes as t1 ON t1.student_id = t.id AND t1.class_id = \'' . $class . '\'';

$cond_vals = new stdClass();
$cond_vals->c = 't1.start <= :v1 AND t1.end > :v1';
$cond_vals->v = array(':v1' => $date);
$opts = new stdClass();
$opts->select_expr = 't.id, t.id_number, t.given_name, t.family_name, t.preferred_name, t.date_of_birth, t.gender, t.citizen, t1.start AS _start, t1.end AS _end';
$opts->join_expr = $join;
$opts->groupby = 'GROUP BY t.id';
$students = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'student_students', $cond_vals, 't.given_name', 'ASC', NULL, NULL, $opts)->d;

date_default_timezone_set('Pacific/Auckland');

/** PHPExcel */
require_once dirname(__FILE__) . '/libs/phpexcel/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$path = dirname(__FILE__) . '/tmp/';
$file = 'Students_' . $date .'.xlsx';

// Set properties
$objPHPExcel->getProperties()->setCreator("Weida")
               ->setLastModifiedBy("Weida")
               ->setTitle('Students')
               ->setSubject('')
               ->setDescription('')
               ->setKeywords('')
               ->setCategory("Office 2007 XLSX Document");

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle("Students");

$horizontal = 67;
$vertical = 5;

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'First Name');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', 'Last Name');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', 'Preferred Name');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', 'Birth Date');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', 'Gender');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', 'Citizenship');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', 'From');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', 'To');

foreach ($students as $idx => &$student) {
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($idx + 2), $student->id_number);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($idx + 2), $student->given_name);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($idx + 2), $student->family_name);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($idx + 2), $student->preferred_name);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($idx + 2), $student->date_of_birth);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($idx + 2), $student->gender == 0 ? 'Male' : 'Female');
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($idx + 2), $student->citizen);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($idx + 2), $student->_start);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($idx + 2), $student->_end);
}


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $file . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>